/*
 *  Netzpult - Your desk on the web
 *  Copyright (C) 2011  Ulrich Hilger, http://uhilger.de
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see http://www.gnu.org/licenses/
 */

package de.uhilger.netzpult.server.db;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.commons.lang.StringEscapeUtils;

import de.uhilger.baselink.PersistenceManager;
import de.uhilger.baselink.Record;
import de.uhilger.baselink.GenericRecord;
import de.uhilger.netzpult.server.AbstractWebfilesServlet;
import de.uhilger.netzpult.shared.ArchivedDocument;
import de.uhilger.netzpult.shared.Document;
import de.uhilger.netzpult.shared.DocumentOwner;
import de.uhilger.netzpult.shared.Folder;
import de.uhilger.netzpult.shared.FolderDocument;
import de.uhilger.netzpult.shared.FolderOwner;
import de.uhilger.netzpult.shared.GroupShare;
import de.uhilger.netzpult.shared.UserGroup;
import de.uhilger.netzpult.shared.UserShare;


/**
 * Central database adapter class with all methods that work on database content
 * 
 * @author Copyright (c) Ulrich Hilger, http://uhilger.de
 * @author Published under the terms and conditions of
 * the <a href="http://www.gnu.org/licenses/" target="_blank">GNU General Public License</a>
 */
public class DataStorageImpl extends PersistenceManager implements DataStorage {
	
	public static final String SCHEMA = "webfiles";
	
	private SimpleDateFormat tsFormatter = new SimpleDateFormat("yyyyMMddHHmmssSSS");
	private String dbtype = AbstractWebfilesServlet.DB_TYPE_MYSQL;
	
	private Record documentMapper = new GenericRecord(Document.class);
	private Record archivedDocumentMapper = new GenericRecord(ArchivedDocument.class);
	private Record documentOwnerMapper = new GenericRecord(DocumentOwner.class);
	private Record folderOwnerMapper = new GenericRecord(FolderOwner.class);
	private Record folderMapper = new GenericRecord(Folder.class);
	private Record folderDocumentMapper = new GenericRecord(FolderDocument.class);
	private Record userShareMapper = new GenericRecord(UserShare.class);

	/**
	 * Eine neue Instanz der Klasse DerbyDataStorage erstellen
	 * @param productionMode  verwendet eine JDBC DataSource vom Servlet container, 
	 * wenn hier Webfiles.PRODUCTION_MODE uebergeben wird, sonst eine lokale Datenbankverbindung 
	 */
	public DataStorageImpl(String productionMode, String dbType, String url) {
		super(productionMode, "jdbc/WebfilesDB", url);
		this.productionMode = productionMode;
		this.dbtype = dbType;
		this.dburl = url;
	}
	
	/* ---------------------- Document Management ----------------------- */
	
	/**
	 * Create a completely new document
	 * 
	 * @param document
	 *          the document to create
	 * @param owner
	 *          user id of document owner
	 * @return the document that was created
	 * @throws Exception
	 *           whenever something goes wrong
	 */
	@Override
	public Document createDocument(Document document, String owner) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("DerbyDataStorage.createDocument");
		Connection c = null;
		try {
			c = getConnection();
			c.setAutoCommit(false);
			int nextKey = incrementKey(c, DataStorage.KEY_DOC_ID);
			if (nextKey > -1) {
				Date date = new Date();
				document.setCreationDateTime(date);
				document.setCreationTime(tsFormatter.format(date));
				document.setId(nextKey);
				if(document.getVersion() == Document.NEW_VERSION_ID) {
					document.setVersion(1);
				}
				insert(c, document, documentMapper);
				insert(c, new DocumentOwner(document.getId(), owner), documentOwnerMapper);
				c.commit();
				c.close();
				c = null;
			} else {
				throw new Exception("next key is -1");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			c.rollback();
		} finally {
			closeConnectionFinally(c);
		}
		return document;
	}
	
	public Document createDocument(Document document, String owner, InputStream content, int len) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("DerbyDataStorage.createDocument");
		Connection c = getConnection();
		c.setAutoCommit(false);
		try {
			//setSchema(c);
			int nextKey = incrementKey(c, DataStorage.KEY_DOC_ID);
			if (nextKey > -1) {
				Date date = new Date();
				document.setCreationDateTime(date);
				document.setCreationTime(tsFormatter.format(date));
				document.setId(nextKey);
				if(document.getVersion() == Document.NEW_VERSION_ID) {
					document.setVersion(1);
				}
				insertDocument(c, document, content, len);
				insert(c, new DocumentOwner(document.getId(), owner), documentOwnerMapper);
				c.commit();
				c.close();
			} else {
				c.rollback();
				c.close();
				throw new Exception("next key is -1");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			c.rollback();
			c.close();
		}
		return document;
	}

	public void createNewVersion(Document document, String owner, InputStream content, int len) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("createNewVersion (stream)");
		if (document.getId() > Document.NEW_DOCUMENT_ID) {
			Connection c = getConnection();
			//setSchema(c);
			try {
				c.setAutoCommit(false);
				boolean isDraft = (document.getVersion() == Document.DRAFT_VERSION_ID);
				int id = document.getId();
				int lastVersion = getLastVersion(id, WITHOUT_DRAFT);
				if(lastVersion == Document.NO_VERSION_ID) {
					Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("createNewVersion: setVersion=1");
					document.setVersion(1);
				} else {
					Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
							"createNewVersion: setVersion=" + (lastVersion + 1));
					document.setVersion(lastVersion + 1);
				}
				Date date = new Date();
				document.setCreationDateTime(date);
				document.setCreationTime(tsFormatter.format(date));
				insertDocument(c, document, content, len);
				updateTextIndex(c, document);
				ResultSet moveDoc = getDocumentAsResultSet(id, lastVersion, WITHOUT_DRAFT);
				if(moveDoc != null) {
					moveDoc.first();
					archiveDocument(c, moveDoc);
					moveDoc.close();
					purgeDocument(c, id, lastVersion);
				}
				if (isDraft) {
					purgeDocument(c, id, Document.DRAFT_VERSION_ID);
				}
				c.commit();
				c.close();
			} catch (Exception ex) {
				c.rollback();
				c.close();
				throw ex;
			}
		} else {
			throw new Exception("DataStorageImpl.createNewVersion Fehler: Falsche Methode, es muss createDocument verwendet werden.");
		}		
	}

	/**
	 * Das &uuml;bergebene Dokument hat entweder eine ID und damit auch eine
	 * Versionsnummer oder noch keine ID. Die folgenden F�lle werden
	 * ber�cksichtigt:
	 * 
	 * 1. 
	 * document.getId()>Document.NEW_DOCUMENT_ID und
	 * document.getVersion()=Document.DRAFT_VERSION_ID:
	 * Die �bergebene Fassung des Dokuments als neue Version speichern und den Entwurf l�schen 
	 * 
	 * 2. 
	 * document.getId()>Document.NEW_DOCUMENT_ID und
	 * document.getVersion()>NEW_VERSION_ID: 
	 * bisher letzte Version nach tabelle
	 * document_archive �bertragen, neues Dokument mit neuer Version speichern
	 * bisher letzte Version aus Tabelle documents l�schen
	 * 
	 * Der Fall 'Neues Dokument' (document.getId() ist -1) wird hier nicht
	 * ber�cksichtigt, daf�r ist die Methode createDokument gedacht
	 */
	@Override
	public Document createNewVersion(Document document) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("createNewVersion");
		if (document.getId() > Document.NEW_DOCUMENT_ID) {
			Connection c = getConnection();
			//setSchema(c);
			try {
				c.setAutoCommit(false);
				boolean isDraft = (document.getVersion() == Document.DRAFT_VERSION_ID);
				int id = document.getId();
				int lastVersion = getLastVersion(id, WITHOUT_DRAFT);
				if(lastVersion == Document.NO_VERSION_ID) {
					Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("createNewVersion: setVersion=1");
					document.setVersion(1);
				} else {
					Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
							"createNewVersion: setVersion=" + (lastVersion + 1));
					document.setVersion(lastVersion + 1);
				}
				Date date = new Date();
				document.setCreationDateTime(date);
				document.setCreationTime(tsFormatter.format(date));
				insert(c, document, documentMapper);
				updateTextIndex(c, document);
				Document moveDoc = getDocument(id, lastVersion, DataStorage.WITH_CONTENT);
				if(moveDoc != null) {
					insert(c, new ArchivedDocument(moveDoc), archivedDocumentMapper);
					purgeDocument(c, id, lastVersion);
				}
				if (isDraft) {
					purgeDocument(c, id, Document.DRAFT_VERSION_ID);
				}
				c.commit();
				c.close();
			} catch (Exception ex) {
				c.rollback();
				c.close();
				throw ex;
			}
		} else {
			throw new Exception("DataStorageImpl.createNewVersion Fehler: Falsche Methode, es muss createDocument verwendet werden.");
		}
		return document;
	}

	@Override
	public Document saveDraft(Document document) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("entry doc id=" + document.getId() + " version=" + document.getVersion());
		Connection c = getConnection();
		//setSchema(c);
		int id = document.getId();
		if(id == Document.NEW_DOCUMENT_ID) {
			throw new Exception("DataStorageImpl.saveDraft Fehler: Falsche Methode, es muss createDocument verwendet werden.");
		} else {
			document.setVersion(Document.DRAFT_VERSION_ID);
			Date date = new Date();
			document.setCreationDateTime(date);
			document.setCreationTime(tsFormatter.format(date));
			Document draftDoc = getDocument(document.getId(), Document.DRAFT_VERSION_ID, DataStorage.WITH_CONTENT);
			if(draftDoc == null) {
				Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("saving draft, inserting id=" + document.getId() + " version=" + document.getVersion());
				insert(c, document, documentMapper);
			} else {
				Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("saving draft, updating id=" + document.getId() + " version=" + document.getVersion());
				update(c, document, documentMapper);
			}
		}
		c.close();
		return document;
	}
	
	public List<Document> findDocuments(String owner, String searchItem) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("entry");
		String[] words = searchItem.split(" ");
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct ts.dc_id, d.dc_version, d.dc_title, d.dc_timestamp, d.dc_mimetype");
		sql.append(" from ");
		sql.append(SCHEMA);
		sql.append(".text_search as ts, ");
		sql.append(SCHEMA);
		sql.append(".documents as d, ");
		sql.append(SCHEMA);
		sql.append(".document_owners as o");
		sql.append(" where ts.dc_id = d.dc_id");
		sql.append(" and ts.dc_id = o.dc_id");
		sql.append(" and (");
		for(int i = 0; i < words.length; i++) {
			if(i > 0) {
				sql.append(" or ");
			}
			sql.append("lower(ts_content) like ");//?");
			sql.append("'%");
			sql.append(words[i].toLowerCase());
			sql.append("%'");
		}
		sql.append(")");
		if(owner != null && owner.length() > 0 && !owner.equalsIgnoreCase(AbstractWebfilesServlet.ANONYMOUS_OWNER)) {
			sql.append(" and o.us_id = '");//?");
			sql.append(owner);
			sql.append("'");
		}
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(sql.toString());
		
		Connection c = null;
		List<Document> documents = new ArrayList<Document>();
		try {
			c = getConnection();
			documents = select(c, sql.toString(), documentMapper, Record.WITHOUT_BLOBS);
			for(int i = documents.size()-1; i >= 0; i--) {
				if(!isDocPublic(c, documents.get(i).getId())) {
					documents.remove(i);
				}
			}
			c.close();
			c = null;
		}
		catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
		}
		finally {
			closeConnectionFinally(c);
		}
		return documents;		
	}
	
	public boolean isPermitted(String userId, int documentId, int requestedPermission) throws Exception {
		boolean isPermitted = false;
		if(isPublic(documentId)) {
			isPermitted = true;
		} else {
			String owner = getDocumentOwner(documentId);
			if(owner.equals(userId)) {
				isPermitted = true;
			} else {
				int permission = getUserPermission(userId, documentId);
				if(permission >= requestedPermission) {
					isPermitted = true;
				} else {
					permission = getGroupPermission(userId, documentId);
					if(permission >= requestedPermission) {
						isPermitted = true;
					}
				}
			}
		}
		return isPermitted;
	}
	
	private int getUserPermission(String userId, int documentId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
		sql.append(SCHEMA);
		sql.append(".document_shares");
		sql.append(" where dc_id = ");
		sql.append(documentId); 
		sql.append(" and us_id = '");
		sql.append(userId);
		sql.append("'");
		List<?> list = select(sql.toString(), userShareMapper, Record.WITHOUT_BLOBS);
		UserShare share = (UserShare) list.get(0);
		return share.getRights();
	}
	
	private int getGroupPermission(String userId, int documentId) {
		int permission = UserShare.NONE;
		return permission;
	}
	
	public void addUserToGroup(int groupId, String userId) {
		Connection c = null;
		PreparedStatement ps = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("insert into ");
			sql.append(SCHEMA);
			sql.append(".group_members");
			sql.append(" (ug_id, us_id)");
			sql.append(" values (?, ?)");
			c = getConnection();
			//setSchema(c);
			c.setAutoCommit(false);
			ps = c.prepareStatement(sql.toString());
			ps.setInt(1, groupId);
			ps.setString(2, userId);
			ps.executeUpdate();
			c.commit();
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
	}
	
	public void removeUserFromGroup(int groupId, String userId) {
		Connection c = null;
		PreparedStatement ps = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("delete from ");
			sql.append(SCHEMA);
			sql.append(".group_members");
			sql.append(" where ug_id = ? and us_id = ?");
			c = getConnection();
			//setSchema(c);
			c.setAutoCommit(false);
			ps = c.prepareStatement(sql.toString());
			ps.setInt(1, groupId);
			ps.setString(2, userId);
			ps.executeUpdate();
			c.commit();
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
	}
	
	public List<UserGroup> getUserGroups(String owner) {
		List<UserGroup> userGroups = new ArrayList<UserGroup>();
		Connection c = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".user_groups");
			sql.append(" where us_id_owner = ?");
			c = getConnection();
			//setSchema(c);
			c.setAutoCommit(false);
			ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, owner);
			rs = ps.executeQuery();
			if(rs.first()) {
				while(!rs.isAfterLast()) {
					UserGroup group = new UserGroup();
					group.setGroupId(rs.getInt("ug_id"));
					group.setGroupName(rs.getString("ug_name"));
					group.setOwner(owner);
					rs.next();
					userGroups.add(group);
				}
			}
			rs.close();
			rs = null;
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeResultSetFinally(rs);
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
		return userGroups;
	}
	
	public UserGroup addUserGroup(UserGroup group) {
		Connection c = null;
		PreparedStatement ps = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("insert into ");
			sql.append(SCHEMA);
			sql.append(".user_groups");
			sql.append(" (ug_id, us_id_owner, ug_name)");
			sql.append(" values (?, ?, ?)");
			c = getConnection();
			//setSchema(c);
			c.setAutoCommit(false);
			ps = c.prepareStatement(sql.toString());
			ps.setInt(1, group.getGroupId());
			ps.setString(2, group.getOwner());
			ps.setString(3, group.getGroupName());
			ps.executeUpdate();
			c.commit();
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
		return group;
	}
	
	public UserGroup updateUserGroups(UserGroup group) {
		Connection c = null;
		PreparedStatement ps = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("update ");
			sql.append(SCHEMA);
			sql.append(".user_groups");
			sql.append(" ug_name = ?");
			sql.append(" where ug_id = ?");
			c = getConnection();
			//setSchema(c);
			c.setAutoCommit(false);
			ps = c.prepareStatement(sql.toString());
			ps.setString(1, group.getGroupName());
			ps.setInt(2, group.getGroupId());
			ps.executeUpdate();
			c.commit();
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
		return group;
	}
		
	public UserGroup deleteUserGroup(UserGroup group) {
		Connection c = null;
		PreparedStatement ps = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("delete from ");
			sql.append(SCHEMA);
			sql.append(".user_groups");
			sql.append(" where ug_id = ?");
			c = getConnection();
			//setSchema(c);
			c.setAutoCommit(false);
			ps = c.prepareStatement(sql.toString());
			ps.setInt(1, group.getGroupId());
			ps.executeUpdate();
			c.commit();
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
		return group;
	}
	
	public List<GroupShare> getGroupShares(int docId) {
		List<GroupShare> groupShares = new ArrayList<GroupShare>();
		Connection c = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".document_group_shares");
			sql.append(" where dc_id = ?");
			c = getConnection();
			//setSchema(c);
			ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setInt(1, docId);
			rs = ps.executeQuery();
			if(rs.first()) {
				while(!rs.isAfterLast()) {
					GroupShare share = new GroupShare();
					share.setDocId(rs.getInt("dc_id"));
					share.setGroupId(rs.getInt("ug_id"));
					share.setGroupRights(rs.getInt("gs_rights"));
					rs.next();
					groupShares.add(share);
				}
			}
			rs.close();
			rs = null;
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeResultSetFinally(rs);
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
		return groupShares;
	}
	
	public GroupShare addGroupShare(GroupShare share) {
		Connection c = null;
		PreparedStatement ps = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("insert into ");
			sql.append(SCHEMA);
			sql.append(".document_group_shares");
			sql.append(" (dc_id, ug_id, gs_rights)");
			sql.append(" values (?, ?, ?)");
			c = getConnection();
			//setSchema(c);
			c.setAutoCommit(false);
			ps = c.prepareStatement(sql.toString());
			ps.setInt(1, share.getDocId());
			ps.setInt(2, share.getGroupId());
			ps.setInt(3, share.getGroupRights());
			ps.executeUpdate();
			c.commit();
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
		return share;
	}
	
	public GroupShare updateGroupShare(GroupShare share) {
		Connection c = null;
		PreparedStatement ps = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("update ");
			sql.append(SCHEMA);
			sql.append(".document_group_shares");
			sql.append(" gs_rights = ?");
			sql.append(" where dc_id = ? and ug_id = ?");
			c = getConnection();
			//setSchema(c);
			c.setAutoCommit(false);
			ps = c.prepareStatement(sql.toString());
			ps.setInt(1, share.getGroupRights());
			ps.setInt(2, share.getDocId());
			ps.setInt(3, share.getGroupId());
			ps.executeUpdate();
			c.commit();
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
		return share;
	}
	
	public GroupShare deleteGroupShare(GroupShare share) {
		Connection c = null;
		PreparedStatement ps = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("delete from ");
			sql.append(SCHEMA);
			sql.append(".document_group_shares");
			sql.append(" where dc_id = ? and ug_id = ?");
			c = getConnection();
			//setSchema(c);
			c.setAutoCommit(false);
			ps = c.prepareStatement(sql.toString());
			ps.setInt(1, share.getDocId());
			ps.setInt(2, share.getGroupId());
			ps.executeUpdate();
			c.commit();
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
		return share;
	}
	
	public List<UserShare> getUserShares(int docId) {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("docId = " + docId);
			StringBuffer sql = new StringBuffer();
			sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".document_shares");
			sql.append(" where dc_id = ");
			sql.append(docId);
			return (List<UserShare>) select(sql.toString(), userShareMapper, Record.WITHOUT_BLOBS);			
	}
	
	public UserShare addUserShare(UserShare share) {
		return (UserShare) insert(share, userShareMapper);
	}
	
	public UserShare updateUserShare(UserShare share) {
		return (UserShare) update(share, userShareMapper);
	}
	
	public UserShare deleteUserShare(UserShare share) {
		return (UserShare) delete(share, userShareMapper);
	}
	
	public String getFolderOwner(Folder folder) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".folder_owners");
		sql.append(" where fo_id = ");
		sql.append(folder.getId());
		List<FolderOwner> list = select(sql.toString(), folderOwnerMapper, Record.WITHOUT_BLOBS);
		FolderOwner folderOwner = list.get(0);
		return folderOwner.getUserId();
	}
	
	public Folder moveFolder(Folder folder, int newParentId) {
		Connection c = null;
		PreparedStatement ps = null;
		try {
			int moveOrderNo = folder.getOrder();
			StringBuffer sql2 = new StringBuffer();
			sql2.append("update ");
			sql2.append(SCHEMA);
			sql2.append(".folders set");
			sql2.append(" fo_order = fo_order-1");
			sql2.append(" where fo_parent = ?");
			sql2.append(" and fo_order > ?");
			StringBuffer sql = new StringBuffer();
			sql.append("update folders set");
			sql.append(" fo_parent = ?");
			sql.append(", fo_order = ?");
			sql.append(" where fo_id = ?");
			c = getConnection();
			//setSchema(c);
			c.setAutoCommit(false);
			int lastOrderNo = getLastFolderOrderNo(c, newParentId);
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("lastOrderNo=" + lastOrderNo);
			ps = c.prepareStatement(sql.toString());
			ps.setInt(1, newParentId);
			ps.setInt(2, lastOrderNo + 1);
			ps.setInt(3, folder.getId());
			ps.executeUpdate();
			ps.close();
			ps = null;
			ps = c.prepareStatement(sql2.toString());
			ps.setInt(1, folder.getParentId());
			ps.setInt(2, moveOrderNo);
			ps.executeUpdate();
			c.commit();
			folder.setParentId(newParentId);
			folder.setOrder(lastOrderNo + 1);
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
		return folder;
	}
	
	/* ----------------------- Database Access ----------------------------- */
	
	private void updateTextIndex(Connection c, Document document) throws Exception {
		if(document.getMimeType().toLowerCase().contains("text")) {
			StringBuffer sql = new StringBuffer();
			sql.append("delete from ");
			sql.append(SCHEMA);
			sql.append(".text_search where dc_id = ?");
			PreparedStatement ps = c.prepareStatement(sql.toString());
			int docId = document.getId();
			ps.setInt(1, docId);
			ps.executeUpdate();
			c.commit();
	
			String contentText = document.getContent();
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(contentText);
			contentText = StringEscapeUtils.unescapeHtml(contentText);
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(contentText);
			contentText = contentText.replaceAll("\\<.*?\\>", " ");
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(contentText);
			contentText = StringEscapeUtils.escapeSql(contentText);
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(contentText);
	
			String[] elements = contentText.split(" ");
			ArrayList<String> strings = new ArrayList<String>();
			for(int i = 0; i < elements.length; i++) {
				String element = elements[i].trim();
				if(element.length() > 0 && !strings.contains(element)) {
					strings.add(element);
				}
			}
			Statement s = c.createStatement();
			Iterator<String> iter = strings.iterator();
			while(iter.hasNext()) {
				String element = iter.next();
				s.addBatch("insert into " + SCHEMA + ".text_search values (" + docId + ", '" + element + "')");
			}
			s.executeBatch();
		}
	}
	
	private void purgeDocument(Connection c, int documentId, int version) throws SQLException {
		Document delDoc = new Document();
		delDoc.setId(documentId);
		delDoc.setVersion(version);
		delete(c, delDoc, documentMapper);
	}

	private void archiveDocument(Connection c, ResultSet document) throws SQLException {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
				"archiveDocument id=" + document.getInt("dc_id") + " version=" + document.getInt("dc_version"));
		StringBuffer sql = new StringBuffer();
		sql.append("insert into ");
			sql.append(SCHEMA);
			sql.append(".document_archive");
		sql.append(" (da_id, da_version, da_title, da_content, da_timestamp, da_mimetype)");
		sql.append(" values (?, ?, ?, ?, ?, ?)");
		PreparedStatement ps = c.prepareStatement(sql.toString());
		ps.setInt(1, document.getInt("dc_id"));
		ps.setInt(2, document.getInt("dc_version"));
		ps.setString(3, document.getString("dc_title"));
		Blob blob = document.getBlob("dc_content");
		ps.setBinaryStream(4, blob.getBinaryStream(), (int) blob.length());
		//ps.setBinaryStream(4, new ByteArrayInputStream(document.getContent().getBytes()), document.getContent().length());
		ps.setString(5, document.getString("dc_timestamp"));
		ps.setString(6, document.getString("dc_mimetype"));
		ps.executeUpdate();
		ps.close();
	}

	private void insertDocument(Connection c, Document document, InputStream content, int len) throws SQLException, IOException {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
				"insertDocument id=" + document.getId() + " version=" + document.getVersion());
		StringBuffer sql = new StringBuffer();
		sql.append("insert into ");
			sql.append(SCHEMA);
			sql.append(".documents");
		sql.append(" (dc_id, dc_version, dc_title, dc_content, dc_timestamp, dc_mimetype)");
		sql.append(" values (?, ?, ?, ?, ?, ?)");
		PreparedStatement ps = c.prepareStatement(sql.toString());
		ps.setInt(1, document.getId());
		ps.setInt(2, document.getVersion());
		ps.setString(3, document.getTitle());
		if(len > 0) {
			ps.setBinaryStream(4, content, len);
		} else {
			ps.setBlob(4, content);
		}
		ps.setString(5, document.getCreationTime());
		ps.setString(6, document.getMimeType());
		ps.executeUpdate();
		ps.close();
	}
		
	/**
	 * Increment key by reading its current value and incrementing it by 1.
	 * Assumes setAutoCommit true/false to be set elsewhere as required prior to
	 * this method call.
	 * 
	 * @param keyName
	 * @return
	 * @throws SQLException
	 */
	private int incrementKey(Connection c, String keyName) throws SQLException {
		int nextKey = getNextKey(c, keyName);
		StringBuffer sql = new StringBuffer();
		sql.append("update ");
			sql.append(SCHEMA);
			sql.append(".keytable");
		sql.append(" set key_next = ?");
		sql.append(" where key_name = ?");
		sql.append(" and key_next = ?");
		PreparedStatement ps = c.prepareStatement(sql.toString());
		ps.setInt(1, nextKey + 1);
		ps.setString(2, keyName);
		ps.setInt(3, nextKey);
		int rowCount = ps.executeUpdate();
		ps.close();
		if (rowCount > 0) {
			return nextKey;
		} else {
			return -1;
		}
	}

	private int getNextKey(Connection c, String keyName) throws SQLException {
		int nextKey = -1;
		StringBuffer sql = new StringBuffer();
		sql.append("select key_next from ");
			sql.append(SCHEMA);
			sql.append(".keytable");
		sql.append(" where key_name = ?");
		PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		ps.setString(1, keyName);
		ResultSet rs = ps.executeQuery();
		if (rs.first()) {
			nextKey = rs.getInt("key_next");
		}
		rs.close();
		ps.close();
		return nextKey;
	}

	@Override
	public Document getDocument(int id, int version, boolean withContent) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".documents");
		sql.append(" where dc_id = ");
		sql.append(id);
		sql.append(" and dc_version = ");
		sql.append(version);		
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(sql.toString());
		List<?> list = select(sql.toString(), documentMapper, withContent);
		if(list != null && list.size() > 0) {
			return (Document) list.get(0);
		} else {
			return null;
		}
	}
	
	public ResultSet getDocumentAsResultSet(int id, boolean ignoreDraft) throws Exception {
		return getDocumentAsResultSet(id, -1, ignoreDraft);
	}
	
	public ResultSet getDocumentAsResultSet(int id, int version, boolean ignoreDraft) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("entry");
		if(version < 0) {
			version = getLastVersion(id, ignoreDraft);
		}
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("id=" + id + " last version=" + version);
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".documents");
		sql.append(" where dc_id = ?");
		sql.append(" and dc_version = ?");
		Connection c = getConnection();
		//setSchema(c);
		PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		ps.setInt(1, id);
		ps.setInt(2, version);
		rs = ps.executeQuery();
		if(rs == null) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("result set is null");
		}
		return rs;
	}
	
	public void close(ResultSet rs) throws SQLException {
		Statement s = rs.getStatement();
		Connection c = s.getConnection();
		rs.close();
		s.close();
		c.close();
	}

	/**
	 * Die letzte Versionsnummer eines Dokuments finden
	 * 
	 * @param documentId  ID des Dokuments
	 * @param ignoreDraft  true, wenn die zulezt gespeicherte Version ermittelt werden soll, die kein 
	 * Entwurf ist, false, wenn der aktuelle Entwurf einbezogen werden soll
	 * @return die letzte Versionsnummer dieses Dokuments
	 */
	@Override
	public int getLastVersion(int documentId, boolean ignoreDraft) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".documents");
		sql.append(" where dc_id = ");
		sql.append(documentId);
		if(ignoreDraft) {
			sql.append(" and dc_version > 0");
		}
		sql.append(" order by dc_timestamp desc");
		List<Document> list = select(sql.toString(), documentMapper, Record.WITHOUT_BLOBS);
		if(list != null && list.size() > 0) {
			Document firstDoc = list.get(0);
			return firstDoc.getVersion();
		} else {
			return Document.NO_VERSION_ID;
		}
	}

	/*private void setSchema(Connection c) throws SQLException {
		String sql = "set schema webfiles";
		if(dbtype.equals(AbstractWebfilesServlet.DB_TYPE_MYSQL)) {
			sql = "use webfiles";
		}
		PreparedStatement ps = c.prepareStatement(sql);
		ps.executeUpdate();
		ps.close();
	}*/

	@Override
	public String getDocumentOwner(int documentId) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".document_owners");
		sql.append(" where dc_id = ");
		sql.append(documentId);
		List<DocumentOwner> list = select(sql.toString(), documentOwnerMapper, Record.WITHOUT_BLOBS);
		DocumentOwner first = list.get(0);
		return first.getUserId();
	}
	
	private int getLastFolderDocumentOrderNo(Connection c, int folderId) throws SQLException {
		int lastNo = 1;
		StringBuffer sql = new StringBuffer();
		sql.append("select max(fo_order) as lastOrderNo from ");
			sql.append(SCHEMA);
			sql.append(".folder_documents");
		sql.append(" where fo_id = ?");
		PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		ps.setInt(1, folderId);
		ResultSet rs = ps.executeQuery();
		if (rs.first()) {
			lastNo = rs.getInt("lastOrderNo");
		}
		rs.close();
		ps.close();
		return lastNo;
	}
	
	private int getLastFolderOrderNo(Connection c, int parentFolderId) throws SQLException {
		int lastNo = 1;
		StringBuffer sql = new StringBuffer();
		sql.append("select max(fo_order) as lastOrderNo from ");
			sql.append(SCHEMA);
			sql.append(".folders");
		sql.append(" where fo_parent = ?");
		PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		ps.setInt(1, parentFolderId);
		ResultSet rs = ps.executeQuery();
		if (rs.first()) {
			lastNo = rs.getInt("lastOrderNo");
		}
		rs.close();
		ps.close();
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("last order for parent folder " + parentFolderId + " is " + lastNo);
		return lastNo;
	}
	
	private int getFolderDocumentOrderNo(Connection c, Folder folder, int documentId) throws SQLException {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".folder_documents");
		sql.append(" where fo_id = ");
		sql.append(folder.getId());
		sql.append(" and dc_id = ");
		sql.append(documentId);
		List<FolderDocument> list = select(c, sql.toString(), folderDocumentMapper, Record.WITHOUT_BLOBS);
		FolderDocument first = list.get(0);
		return first.getFolderOrder();
	}
	
	private void updateFolderDocumentOrder(Connection c, Folder folder, int orderNo) throws SQLException {
		StringBuffer sql = new StringBuffer();
		sql.append("update ");
			sql.append(SCHEMA);
			sql.append(".folder_documents");
		sql.append(" set fo_order = fo_order-1");
		sql.append(" where fo_id = ?");
		sql.append(" and fo_order > ?");
		PreparedStatement ps = c.prepareStatement(sql.toString());
		ps.setInt(1, folder.getId());
		ps.setInt(2, orderNo);
		ps.executeUpdate();
		ps.close();
	}
	
	private void setFolderOrderNo(Connection c, int folderId, int orderNo) throws SQLException {
		StringBuffer sql = new StringBuffer();
		sql.append("update ");
			sql.append(SCHEMA);
			sql.append(".folders");
		sql.append(" set fo_order = ?");
		sql.append(" where fo_id = ?");
		PreparedStatement ps = c.prepareStatement(sql.toString());
		ps.setInt(1, orderNo);
		ps.setInt(2, folderId);
		ps.executeUpdate();
		ps.close();
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
				"folder " + folderId + " set to order " + orderNo);
	}
	
	private Document getFolderDocument(Connection c, int folderId, int orderNo) throws Exception {
		Document document = null;
			StringBuffer sql = new StringBuffer();
			sql.append("select d.*, fd.fo_order from ");
			sql.append(SCHEMA);
			sql.append(".documents as d, ");
			sql.append(SCHEMA);
			sql.append(".folder_documents as fd");
			sql.append(" where fd.fo_id = ?");
			sql.append(" and fd.fo_order = ?");
			sql.append(" and fd.dc_id = d.dc_id");
			PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setInt(1, folderId);
			ps.setInt(2, orderNo);
			ResultSet rs = ps.executeQuery();
			if (rs.first()) {
					document = rsToDocument(rs, false);
					document.setOrder(rs.getInt("fo_order"));
			}
			rs.close();
			ps.close();
		return document;
	}
	
	private Document getDocument(Connection c, int folderId, int documentId) throws Exception {
		Document document = null;
			StringBuffer sql = new StringBuffer();
			sql.append("select d.*, fd.fo_order from ");
			sql.append(SCHEMA);
			sql.append(".documents as d, ");
			sql.append(SCHEMA);
			sql.append(".folder_documents as fd");
			sql.append(" where fd.dc_id = ?");
			sql.append(" and fd.fo_id = ?");
			sql.append(" and fd.dc_id = d.dc_id");
			PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setInt(1, documentId);
			ps.setInt(2, folderId);
			ResultSet rs = ps.executeQuery();
			if (rs.first()) {
					document = rsToDocument(rs, false);
					document.setOrder(rs.getInt("fo_order"));
			}
			rs.close();
			ps.close();
		return document;
	}

	private Folder getFolder(Connection c, int folderId) throws SQLException {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".folders");
		sql.append(" where fo_id = ");
		sql.append(folderId);
		List<Folder> folderList = select(c, sql.toString(), folderMapper, Record.WITHOUT_BLOBS);
		if(folderList != null && folderList.size() > 0) {
			return folderList.get(0);
		} else {
			return null;
		}
	}
	
	private Folder getFolder(Connection c, int parentId, int orderNo) throws SQLException {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".folders");
		sql.append(" where fo_parent = ");
		sql.append(parentId);
		sql.append(" and fo_order = ");
		sql.append(orderNo);
		List<Folder> folderList = select(c, sql.toString(), folderMapper, Record.WITHOUT_BLOBS);
		if(folderList != null && folderList.size() > 0) {
			return folderList.get(0);
		} else {
			return null;
		}
	}

	private List<Folder> getFolder(Connection c, int parentId, String name) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".folders");
		sql.append(" where fo_name = '");
		sql.append(name);
		sql.append("'");
		sql.append(" and fo_parent = ");
		sql.append(parentId);
		return select(c, sql.toString(), folderMapper, Record.WITHOUT_BLOBS);
	}

	private List<Folder> getChildren(Connection c, int parentId) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".folders where fo_parent = ");
		sql.append(parentId);
		sql.append(" order by fo_order");
		return select(c, sql.toString(), folderMapper, Record.WITHOUT_BLOBS);
	}

	private List<Folder> getChildren(Connection c, int parentId, String owner) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("folder " + parentId);
		ArrayList<Folder> list = new ArrayList<Folder>();
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select f.* from ");
			sql.append(SCHEMA);
			sql.append(".folders as f, ");
			sql.append(SCHEMA);
			sql.append(".folder_owners as fo");
			sql.append(" where fo_parent = ?");
			sql.append(" and fo.fo_id = f.fo_id");
			sql.append(" and fo.us_id = ?");
			sql.append(" order by f.fo_order");
			PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setInt(1, parentId);
			ps.setString(2, owner);
			ResultSet rs = ps.executeQuery();
			if (rs.first()) {
				while(!rs.isAfterLast()) {
					list.add(rsToFolder(rs));
					rs.next();
				}
			}
			rs.close();
			ps.close();
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
			throw ex;
		}
		return list;
	}
	
	private int getParentFolderId(Connection c, int folderId) throws Exception {
		int id = Folder.ROOT_FOLDER_ID;
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".folders where fo_id = ");
		sql.append(folderId);
		List<Folder> folderList = select(c, sql.toString(), folderMapper, Record.WITHOUT_BLOBS);
		if(folderList != null && folderList.size() > 0) {
			id = folderList.get(0).getParentId();
		}
		return id;
	}
	
	private List<Folder> getFoldersContaining(Connection c, int documentId) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("documentId=" + documentId);
		StringBuffer sql = new StringBuffer();
		sql.append("select fo_id from ");
			sql.append(SCHEMA);
			sql.append(".folder_documents");
		sql.append(" where dc_id = ?");
		PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		ps.setInt(1, documentId);
		List<Folder> list = new ArrayList<Folder>();
		ResultSet rs = ps.executeQuery();
		if(rs.first()) {
			while(!rs.isAfterLast()) {
				int folderId = rs.getInt("fo_id");
				if(isFolderPublic(c, folderId)) {
					Folder folder = this.getFolder(c, folderId);
					if(folder == null && folderId == Folder.PUBLIC_FOLDER_ID) {
						folder = Folder.getPublicFolder();
					}
					list.add(folder);
					Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("is in folder " + 
							folder.getId() + " " + folder.getName());
				}
				rs.next();
			}
		}
		return list;
	}
	
	private boolean isFolderPublic(Connection c, int folderId) throws Exception {
		int parentFolderId = getParentFolderId(c, folderId);
		while(parentFolderId != Folder.PUBLIC_FOLDER_ID && parentFolderId != Folder.ROOT_FOLDER_ID) {
			parentFolderId = getParentFolderId(c, parentFolderId);
		}
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("parentFolderId=" + parentFolderId + 
				", folderId=" + folderId + "isFolderPublic=" + 
				(parentFolderId == Folder.PUBLIC_FOLDER_ID || folderId == Folder.PUBLIC_FOLDER_ID));
		return (parentFolderId == Folder.PUBLIC_FOLDER_ID || folderId == Folder.PUBLIC_FOLDER_ID);
	}
	
	private boolean isDocPublic(Connection c, int documentId) throws Exception {
		boolean docIsPublic = false;
		List<Folder> folders = getFoldersContaining(c, documentId);
		Iterator<Folder> i = folders.iterator();
		while(i.hasNext() && !docIsPublic) {
			docIsPublic = isFolderPublic(c, i.next().getId());
		}
		return docIsPublic;
	}

	/* ---------------------- Helper Methods ----------------------------- */
	
	@Override
	public Document getDocument(int id, boolean ignoreDraft, boolean withContent) throws Exception {
		return getDocument(id, getLastVersion(id, ignoreDraft), withContent);
	}


	private Document rsToDocument(ResultSet rs, boolean withContent) throws Exception {
		return (Document) documentMapper.toObject(rs, withContent);
	}
	
	public String getCreationTimeStrLong(String timestamp) {
		Date date = getDocumentDate(timestamp);
		return getCreationTimeStrLong(date);
	}
	
	public String getCreationTimeStrLong(Date date) {
		return new SimpleDateFormat("d. MMMM yyyy HH:mm:ss").format(date);
	}
	
	public Date getDocumentDate(String creationTime) {
		GregorianCalendar cal = new GregorianCalendar();
		cal.set(Calendar.YEAR, Integer.parseInt(creationTime.substring(0, 4)));
		cal.set(Calendar.MONTH, Integer.parseInt(creationTime.substring(4, 6)) - 1);
		cal.set(Calendar.DAY_OF_MONTH, Integer.parseInt(creationTime.substring(6, 8)));
		cal.set(Calendar.HOUR_OF_DAY, Integer.parseInt(creationTime.substring(8, 10)));
		cal.set(Calendar.MINUTE, Integer.parseInt(creationTime.substring(10, 12)));
		cal.set(Calendar.SECOND, Integer.parseInt(creationTime.substring(12, 14)));		
		return cal.getTime();
	}

	private Folder rsToFolder(ResultSet rs) throws Exception {
		return (Folder) folderMapper.toObject(rs, Record.WITHOUT_BLOBS);
	}

	/* --------------- Folder Management -------------------- */
	
	@Override
	public void addToFolder(Folder folder, int documentId) throws Exception {
		Connection c = getConnection();
		c.setAutoCommit(false);
		//setSchema(c);
		int fId = folder.getId();
		insert(c, new FolderDocument(fId, documentId, getLastFolderDocumentOrderNo(c, fId) + 1), folderDocumentMapper);
		c.commit();
		c.close();
	}
	
	public int getLastFolderOrderNo(int parentFolderId) throws Exception {
		int lastNo = 1;
		Connection c = getConnection();
		//setSchema(c);
		lastNo = getLastFolderOrderNo(c, parentFolderId);
		c.close();
		return lastNo;
	}

	@Override
	public int upFolder(int folderId) throws Exception {
		Connection c = getConnection();
		c.setAutoCommit(false);
		try {
			//setSchema(c);
			Folder folderUp = getFolder(c, folderId);
			int orderNo = folderUp.getOrder();
			if(orderNo > 1) {
				int newOrderNo = orderNo-1;
				Folder folderDown = getFolder(c, folderUp.getParentId(), newOrderNo);
				setFolderOrderNo(c, folderDown.getId(), orderNo);
				setFolderOrderNo(c, folderUp.getId(), newOrderNo);
				c.commit();
				c.close();
				Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
						"folder " + folderUp.getName() + " moved from " + orderNo + " to " + newOrderNo);
				return newOrderNo;
			} else {
				throw new Exception("DataStorageImpl.upFolder: Folder is already first.");
			}
		} catch(Exception ex) {
			c.rollback();
			c.close();
			throw ex;
		}
	}

	@Override
	public int downFolder(int folderId) throws Exception {
		Connection c = getConnection();
		c.setAutoCommit(false);
		try {
			//setSchema(c);
			Folder folderDown = getFolder(c, folderId);
			int lastOrderNo = getLastFolderOrderNo(c, folderDown.getParentId());
			int orderNo = folderDown.getOrder();
			if(orderNo < lastOrderNo) {
				int newOrderNo = orderNo+1;
				Folder folderUp = getFolder(c, folderDown.getParentId(), newOrderNo);
				setFolderOrderNo(c, folderUp.getId(), orderNo);
				setFolderOrderNo(c, folderDown.getId(), newOrderNo);
				c.commit();
				c.close();
				Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
						"folder " + folderDown.getName() + " moved from " + orderNo + " to " + newOrderNo);
				return newOrderNo;
			} else {
				throw new Exception("DataStorageImpl.downFolder: Folder is already last.");
			}
		} catch(Exception ex) {
			c.rollback();
			c.close();
			throw ex;
		}
	}

	@Override
	public int upDocument(int folderId, int documentId) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
				"folder " + folderId +", document " + documentId);
		Connection c = getConnection();
		c.setAutoCommit(false);
		try {
			//setSchema(c);
			Folder folder = getFolder(c, folderId);
			Document upDocument = getDocument(c, folder.getId(), documentId);
			int orderNo = upDocument.getOrder();
			if(orderNo > 1) {
				int newOrderNo = orderNo-1;
				Document downDocument = getFolderDocument(c, folder.getId(), newOrderNo);
				update(c, new FolderDocument(folder.getId(), downDocument.getId(), orderNo), folderDocumentMapper);
				update(c, new FolderDocument(folder.getId(), upDocument.getId(), newOrderNo), folderDocumentMapper);
				c.commit();
				c.close();
				Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
						"folder " + folder.getName() +", document " + upDocument.getTitle() + 
						" moved from " + orderNo + " to " + newOrderNo);
				return newOrderNo;
			} else {
				throw new Exception("DataStorageImpl.upDocument: Document is already first.");
			}
		} catch(Exception ex) {
			c.rollback();
			c.close();
			throw ex;
		}
	}

	@Override
	public int downDocument(int folderId, int documentId) throws Exception {
		Connection c = getConnection();
		c.setAutoCommit(false);
		try {
			//setSchema(c);
			Folder folder = getFolder(c, folderId);
			Document downDocument = getDocument(c, folder.getId(), documentId);
			int lastOrderNo = getLastFolderDocumentOrderNo(c, folder.getId());
			int orderNo = downDocument.getOrder();
			if(orderNo < lastOrderNo) {
				int newOrderNo = orderNo+1;
				Document upDocument = this.getFolderDocument(c, folder.getId(), newOrderNo);
				update(c, new FolderDocument(folder.getId(), upDocument.getId(), orderNo), folderDocumentMapper);
				update(c, new FolderDocument(folder.getId(), downDocument.getId(), newOrderNo), folderDocumentMapper);
				c.commit();
				c.close();
				Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
						"folder " + folder.getName() + ", document " + downDocument.getTitle() + 
						" moved from " + orderNo + " to " + newOrderNo);
				return newOrderNo;
			} else {
				throw new Exception("DataStorageImpl.downFolder: Folder is already last.");
			}
		} catch(Exception ex) {
			c.rollback();
			c.close();
			throw ex;
		}
	}
	
	@Override
	public Folder createFolder(Folder folder, String owner) throws Exception {
		Connection c = null;
		try {
			c = getConnection();
			c.setAutoCommit(false);
			//setSchema(c);
			int lastOrderNo = getLastFolderOrderNo(c, folder.getParentId());
			int nextKey = incrementKey(c, DataStorage.KEY_FOLDER_ID);
			if (nextKey > -1) {
				folder.setId(nextKey);
				folder.setOrder(lastOrderNo + 1);
				insert(c, folder, folderMapper);
				insert(c, new FolderOwner(folder.getId(), owner), folderOwnerMapper);
				c.commit();
				c.close();
				c = null;
				Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("folder " + folder.getName() + " created with order no " + (lastOrderNo + 1));
			} else {
				throw new Exception("next key is -1");
			}
		} catch (Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
			c.rollback();
			throw ex;
		} finally {
			closeConnectionFinally(c);
		}
		return folder;
	}

	@Override
	public void deleteFolder(Folder folder) throws Exception {
		throw new Exception("DataStoreImpl.deleteFolder not implemented.");
		/* Wichtig: beim Entfernen eines Ordners die Reihenfolge 
		 * der verbleibenden Ordner berichtigen!
		 * analog zu updateFolderDocumentOrder
		 */
	}

	@Override
	public List<Folder> getChildren(Folder folder) throws Exception {
		Connection c = getConnection();
		//setSchema(c);
		List<Folder> list = getChildren(c, folder.getId());
		c.close();
		return list;
	}

	@Override
	public List<Folder> getChildren(Folder folder, String owner) throws Exception {
		Connection c = getConnection();
		//setSchema(c);
		List<Folder> list = getChildren(c, folder.getId(), owner);
		c.close();
		return list;
	}
	
	public List<Document> getDocuments(String owner, Folder folder, boolean withoutDraft, String orderClause, 
			int offset, int count) throws Exception {
		ArrayList<Document> list = new ArrayList<Document>();
		Connection c = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			c = getConnection();
			//setSchema(c);

			StringBuffer sql = new StringBuffer();
				sql.append("select d.dc_id, d.dc_version, d.dc_title, d.dc_timestamp, d.dc_mimetype");
			sql.append(", fd.fo_order from ");
			sql.append(SCHEMA);
			sql.append(".documents as d, ");
			sql.append(SCHEMA);
			sql.append(".folder_documents as fd, ");
			sql.append(SCHEMA);
			sql.append(".document_owners as do");
			sql.append(" where do.dc_id = d.dc_id");
			sql.append(" and fd.dc_id = d.dc_id");
			sql.append(" and do.us_id = ?");
			sql.append(" and fd.fo_id = ?");
			if(withoutDraft) {/* && folderId != Folder.NOT_FILED_FOLDER_ID) { // drafts einschließen, wenn "nicht abgelegt"*/
				sql.append(" and d.dc_version <> ?");
			}
			if(orderClause == null || orderClause.length() < 1) {
				sql.append(" order by fd.fo_order");
			} else {
				sql.append(" ");
				sql.append(orderClause);
			}
			sql.append(" offset ");
			sql.append(offset);
			sql.append(" rows");
			sql.append(" fetch first ");
			sql.append(count);
			sql.append(" rows only");
			
			ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, owner);
			ps.setInt(2, folder.getId());
			if(withoutDraft) { // && folderId != Folder.NOT_FILED_FOLDER_ID) { // drafts einschließen, wenn "nicht abgelegt"
				ps.setInt(3, Document.DRAFT_VERSION_ID);
			}
			rs = ps.executeQuery();
			if (rs.first()) {
				while(!rs.isAfterLast()) {
					Document doc = rsToDocument(rs, false);
					doc.setOrder(rs.getInt("fo_order"));
					list.add(doc);
					Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
							"added document " + doc.getId() + " " + doc.getTitle());
					rs.next();
				}
			}
			
			rs.close();
			rs = null;
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeResultSetFinally(rs);
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
		return list;
	}

	@Override
	public List<Document> getDocuments(String owner, Folder folder, boolean withoutDraft) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
				"owner=" + owner + " folder " + folder.getId() + " " + folder.getName());
		Connection c = getConnection();
		//setSchema(c);
		ArrayList<Document> list = new ArrayList<Document>();
		try {
			StringBuffer sql = new StringBuffer();
				sql.append("select d.dc_id, d.dc_version, d.dc_title, d.dc_timestamp, d.dc_mimetype");
			sql.append(", fd.fo_order from ");
			sql.append(SCHEMA);
			sql.append(".documents as d, ");
			sql.append(SCHEMA);
			sql.append(".folder_documents as fd, ");
			sql.append(SCHEMA);
			sql.append(".document_owners as do");
			sql.append(" where do.dc_id = d.dc_id");
			sql.append(" and fd.dc_id = d.dc_id");
			sql.append(" and do.us_id = ?");
			sql.append(" and fd.fo_id = ?");
			if(withoutDraft) {/* && folderId != Folder.NOT_FILED_FOLDER_ID) { // drafts einschließen, wenn "nicht abgelegt"*/
				sql.append(" and d.dc_version <> ?");
			}
			sql.append(" order by fd.fo_order");
			PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, owner);
			ps.setInt(2, folder.getId());
			if(withoutDraft) { // && folderId != Folder.NOT_FILED_FOLDER_ID) { // drafts einschließen, wenn "nicht abgelegt"
				ps.setInt(3, Document.DRAFT_VERSION_ID);
			}
			ResultSet rs = ps.executeQuery();
			if (rs.first()) {
				while(!rs.isAfterLast()) {
					Document doc = rsToDocument(rs, false);
					doc.setOrder(rs.getInt("fo_order"));
					list.add(doc);
					Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
							"added document " + doc.getId() + " " + doc.getTitle());
					rs.next();
				}
			}
			rs.close();
			ps.close();
			c.close();
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
			c.close();
			throw ex;
		}
		return list;
	}
	
	@Override
	public List<Folder> getRootFolders(String owner) throws Exception {
		Connection c = getConnection();
		//setSchema(c);
		ArrayList<Folder> list = new ArrayList<Folder>();
		list.add(Folder.getDraftFolder());
		list.add(Folder.getNotFiledFolder());
		list.add(Folder.getSharedFolder());
		list.add(Folder.getPublicFolder());
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select f.* from ");
			sql.append(SCHEMA);
			sql.append(".folders as f, ");
			sql.append(SCHEMA);
			sql.append(".folder_owners as fo");
			sql.append(" where fo.fo_id = f.fo_id");
			sql.append(" and fo.us_id = ?");
			sql.append(" and f.fo_parent = ?");
			PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, owner);
			ps.setInt(2, Folder.ROOT_FOLDER_ID);
			ResultSet rs = ps.executeQuery();
			if (rs.first()) {
				while(!rs.isAfterLast()) {
					list.add(rsToFolder(rs));
					rs.next();
				}
			}
			rs.close();
			ps.close();
			c.close();
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
			c.close();
			throw ex;
		}
		return list;
	}

	@Override
	public List<Folder> getPublicRootFolders(String owner) throws Exception {
		Connection c = getConnection();
		//setSchema(c);
		ArrayList<Folder> list = new ArrayList<Folder>();
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select f.* from ");
			sql.append(SCHEMA);
			sql.append(".folders as f, ");
			sql.append(SCHEMA);
			sql.append(".folder_owners as fo");
			sql.append(" where fo.fo_id = f.fo_id");
			sql.append(" and fo.us_id = ?");
			sql.append(" and f.fo_parent = ?");
			PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, owner);
			ps.setInt(2, Folder.PUBLIC_FOLDER_ID);
			ResultSet rs = ps.executeQuery();
			if (rs.first()) {
				while(!rs.isAfterLast()) {
					list.add(rsToFolder(rs));
					rs.next();
				}
			}
			rs.close();
			ps.close();
			c.close();
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
			c.close();
			throw ex;
		}
		return list;
	}

	@Override
	public void removeFromFolder(Folder folder, int documentId) throws Exception {
		Connection c = getConnection();
		c.setAutoCommit(false);
		//setSchema(c);
		int orderNo = getFolderDocumentOrderNo(c, folder, documentId);
		delete(c, new FolderDocument(folder.getId(), documentId, orderNo), folderDocumentMapper);
		//unfile(c, documentId, folder);
		updateFolderDocumentOrder(c, folder, orderNo);
		c.commit();
		c.close();
	}

	@Override
	public Folder updateFolder(Folder folder) throws Exception {
		Connection c = getConnection();
		//setSchema(c);
		//updateFolder(c, folder);
		update(c, folder, folderMapper);
		c.close();
		return folder;
	}

	@Override
	public List<Document> getUnfiledDocuments(String owner, boolean withoutDraft) throws Exception {
		List<Document> drafts = new ArrayList<Document>();
		Connection c = getConnection();
		//setSchema(c);
		ArrayList<Document> list = new ArrayList<Document>();
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select d.dc_id, d.dc_version, d.dc_title, d.dc_timestamp, d.dc_mimetype");
			sql.append(" from ");
			sql.append(SCHEMA);
			sql.append(".documents as d, ");
			sql.append(SCHEMA);
			sql.append(".document_owners as o");
			sql.append(" where d.dc_id = o.dc_id ");
			sql.append(" and o.us_id = ?");
			sql.append(" and d.dc_id not in (select dc_id from ");
			sql.append(SCHEMA);
			sql.append(".folder_documents)");
			PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, owner);
			ResultSet rs = ps.executeQuery();
			if (rs.first()) {
				while(!rs.isAfterLast()) {
					Document doc = rsToDocument(rs, false);
					if(doc.getVersion() == Document.DRAFT_VERSION_ID) {
						drafts.add(doc);
					}
					list.add(doc);
					rs.next();
				}
			}
			rs.close();
			ps.close();
			c.close();
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
			c.close();
			throw ex;
		}
		list.removeAll(drafts);
		return list;
	}

	@Override
	public List<Document> getSharedDocuments(String owner) throws Exception {
		boolean withoutDraft = true;
		ArrayList<Document> list = new ArrayList<Document>();

		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
				"owner=" + owner);
		Connection c = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer();
			if(withoutDraft) {
				sql.append("select d.dc_id, d.dc_version, d.dc_title, d.dc_timestamp, d.dc_mimetype");
			} else {
				sql.append("select d.*");
			}
			sql.append(" from ");
			sql.append(SCHEMA);
			sql.append(".documents as d, ");
			sql.append(SCHEMA);
			sql.append(".document_shares as ds");
			sql.append(" where ds.dc_id = d.dc_id");
			sql.append(" and ds.us_id = ?");
			if(withoutDraft) {
				sql.append(" and d.dc_version <> ?");
			}
			c = getConnection();
			//setSchema(c);
			ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, owner);
			if(withoutDraft) {
				ps.setInt(2, Document.DRAFT_VERSION_ID);
			}
			rs = ps.executeQuery();
			if (rs.first()) {
				while(!rs.isAfterLast()) {
					Document doc = rsToDocument(rs, false);
					list.add(doc);
					Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(
							"added document " + doc.getId() + " " + doc.getTitle());
					rs.next();
				}
			}
			rs.close();
			rs = null;
			ps.close();
			ps = null;
			c.close();
			c = null;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeResultSetFinally(rs);
			closeStatementFinally(ps);
			closeConnectionFinally(c);
		}
		
		return list;
	}
	
	@Override
	public List<Document> getDraftDocuments(String owner) throws Exception {
		Connection c = getConnection();
		//setSchema(c);
		ArrayList<Document> list = new ArrayList<Document>();
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select d.dc_id, d.dc_version, d.dc_title, d.dc_timestamp, d.dc_mimetype");
			sql.append(" from ");
			sql.append(SCHEMA);
			sql.append(".documents as d, ");
			sql.append(SCHEMA);
			sql.append(".document_owners as o");
			sql.append(" where d.dc_id = o.dc_id ");
			sql.append(" and o.us_id = ?");
			sql.append(" and d.dc_version = ?");
			PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, owner);
			ps.setInt(2, Document.DRAFT_VERSION_ID);
			ResultSet rs = ps.executeQuery();
			if (rs.first()) {
				while(!rs.isAfterLast()) {
					list.add(rsToDocument(rs, false));
					rs.next();
				}
			}
			rs.close();
			ps.close();
			c.close();
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
			c.close();
			throw ex;
		}
		return list;
	}

	@Override
	public boolean isPublic(int documentId) throws Exception {
		Connection c = getConnection();
		//setSchema(c);
		boolean docIsPublic = isDocPublic(c, documentId);
		c.close();
		return docIsPublic;
	}

	@Override
	public List<Folder> getFoldersContaining(int documentId) throws Exception {
		Connection c = getConnection();
		//setSchema(c);
		List<Folder> folders = getFoldersContaining(c, documentId);
		c.close();
		return folders;
	}
	
	public List<Folder> getFolderPath(Folder folder) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("entry");
		Connection c = getConnection();
		//setSchema(c);
		List<Folder> list = new ArrayList<Folder>();
		int parentId = folder.getParentId();
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("start parentId=" + parentId);
		folder = getFolder(c, parentId);
		while(parentId != Folder.ROOT_FOLDER_ID && folder != null) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("adding to path: " + folder.getName());
			list.add(0, folder);
			parentId = folder.getParentId();
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("next parentId: " + parentId);
			folder = getFolder(c, parentId);
		}
		c.close();
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("exit");
		return list;
	}

	public List<Folder> getFolder(int parentId, String name) throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("entry");
		Connection c = getConnection();
		//setSchema(c);
		List<Folder> folders = this.getFolder(c, parentId, name);
		c.close();
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("exit");
		return folders;
	}
	
	public String reindex() throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".documents");
		sql.append(" where dc_version > ?");
		sql.append(" and dc_mimetype like ?");
		Connection c = getConnection();
		//setSchema(c);
		c.setAutoCommit(false);
		PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		ps.setInt(1, Document.DRAFT_VERSION_ID);
		ps.setString(2, "text%");
		ResultSet rs = ps.executeQuery();
		while(rs.next()) {
			updateTextIndex(c, rsToDocument(rs, WITH_CONTENT));
		}
		c.commit();
		c.close();
		return "index rebuilt";
	}
	
	/* ---------------------- Export --------------------------------- */
	
	public ResultSet getAllDocuments() throws Exception {
		Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("entry");
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
			sql.append(SCHEMA);
			sql.append(".documents");
		Connection c = getConnection();
		//setSchema(c);
		PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		rs = ps.executeQuery();
		if(rs == null) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest("result set is null");
		}
		return rs;
	}

	@Override
	public int deleteDocument(int docId) throws Exception {
		Connection c = null;
		int deletedId = -1;
		try {
			c = getConnection();
			c.setAutoCommit(false);
			StringBuffer sql = new StringBuffer();
			sql.append("delete from ");
			sql.append(archivedDocumentMapper.getTableName());
			sql.append(" where da_id = ");
			sql.append(docId);
			execute(sql.toString());
			sql = new StringBuffer();
			sql.append("delete from ");
			sql.append(documentMapper.getTableName());
			sql.append(" where dc_id = ");
			sql.append(docId);
			execute(sql.toString());
			c.commit();
			c.close();
			c = null;
			deletedId = docId;
		} catch(Exception ex) {
			Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(Level.SEVERE, ex.getMessage(), ex);
		} finally {
			closeConnectionFinally(c);
		}
		return deletedId;
	}
}
